package com.laboratory.util.office;

import com.laboratory.util.ArrayUtil;
import com.laboratory.util.StringUtil;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author ch
 * @Description
 * @Date 2020/03/16 21:43
 */
public class HandleExcel {
    public static void main(String[] args) {
        handleExcel();



    }

    public static final List<String> ignoreOrderNumList =  new ArrayList<String>(){{
        add("未下单"); add("已下单"); add("货量材"); add("库存料");
    }};

    public static void handleExcel(){
        int count = 0;
        HashSet<String> set = new HashSet<>();
        ignoreOrderNumList.addAll(ignoreOrderNumList);
        try{
            XSSFWorkbook xwb = new XSSFWorkbook("D:\\Program Files\\feiq\\Recv Files\\20200313华北加工计划表-采购回复3.16.xlsx");
            XSSFSheet sheet = xwb.getSheet("03.04.05计划合并 ");
            XSSFRow xRow;

            for (int numRow = 0; numRow <= sheet.getLastRowNum(); numRow++) {

                xRow = sheet.getRow(numRow);
                String orderNum = getValue(xRow.getCell(30));
                String arrivalTime = getValue(xRow.getCell(33));
                 if (ignoreOrderNumList.contains(orderNum)){
                     continue;
                 }
                count++;
                set.add(orderNum);
               System.out.println(""+ orderNum );
            }
            xwb.close();
        }catch (Exception e){
            System.out.println("=====================异常===============================" + count);
        }

        System.out.println("count = " + count);
        System.out.println("set.size() = " + set.size());

        HashMap<String, String> resultMap = new HashMap<>();
        for (String orderNums:set){





        }





    }

   public static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    public static Map<String, Map<String, Object>> getSAP44Mapper(){
        int count = 0;
        HashSet<String> set = new HashSet<>();
        Map<String, Map<String, Object>>  SAPMapper = new HashMap<>();
        try{
            XSSFWorkbook xwb = new XSSFWorkbook("D:\\Program Files\\feiq\\Recv Files\\SAP辅材台账3-16 - 副本.XLSX");
            XSSFSheet sheet = xwb.getSheet("华北");
            XSSFRow xRow;

           for (int numRow = 1; numRow <= sheet.getLastRowNum(); numRow++) {
                xRow = sheet.getRow(numRow);
                String orderNum = getValue(xRow.getCell(1));
                Date arrivalTime =  xRow.getCell(3).getDateCellValue();
                count++;
                set.add(orderNum);
                System.out.println(""+ orderNum + "   " + dateFormat.format(arrivalTime));
                Map<String, Object> singleLine = new HashMap<>();
                singleLine.put("orderNum", orderNum);
                singleLine.put("arrivalTime", arrivalTime);

                SAPMapper.put(orderNum, singleLine);

            }

              sheet = xwb.getSheet("华东");

            for (int numRow = 1; numRow <= sheet.getLastRowNum(); numRow++) {
                xRow = sheet.getRow(numRow);
                String orderNum = getValue(xRow.getCell(1));
                Date arrivalTime =  xRow.getCell(3).getDateCellValue();
                if(arrivalTime == null){
                    continue;
                }

                count++;
                set.add(orderNum);

                System.out.println(""+ orderNum + "   " + dateFormat.format(arrivalTime));
                Map<String, Object> singleLine = new HashMap<>();
                singleLine.put("orderNum", orderNum);
                singleLine.put("arrivalTime", arrivalTime);

                SAPMapper.put(orderNum, singleLine);

            }

        }catch (Exception e){
            System.out.println("=====================异常===============================" + count);
        }

        System.out.println("count = " + count);
        System.out.println(SAPMapper.size());
        System.out.println(set.size());
        return SAPMapper;
    }

    public static Map<String, List<Map<String, Object>>> getFCMapper(){
        int count = 0;
        HashSet<String> set = new HashSet<>();
        Map<String, List<Map<String, Object>>>  FCMapper = new HashMap<>();
        try{
            XSSFWorkbook xwb = new XSSFWorkbook("D:\\Program Files\\feiq\\Recv Files\\供应商联动表-辅料3-14 - 副本.xlsx");
            XSSFSheet sheet = xwb.getSheet("万方订单表");
            XSSFRow xRow;

            for (int numRow = 2; numRow <= sheet.getLastRowNum(); numRow++) {
                xRow = sheet.getRow(numRow);
                String orderNum = getValue(xRow.getCell(1));
                if(!orderNum.contains("FC")){
                    continue;
                }
                try {
                    Date arrivalGoodTime =  xRow.getCell(21).getDateCellValue();
                    dateFormat.format(arrivalGoodTime);
                }catch (Exception e) {
                    continue;
                }
                Date arrivalGoodTime =  xRow.getCell(21).getDateCellValue();
                count++;
                set.add(orderNum);
                System.out.println(""+ orderNum + "   " + dateFormat.format(arrivalGoodTime));
                Map<String, Object> singleLine = new HashMap<>();
                singleLine.put("orderNum", orderNum);
                singleLine.put("arrivalTime", arrivalGoodTime);
                List<Map<String, Object>> list = FCMapper.get(orderNum);
                if(list == null){
                    list = new ArrayList<>();
                }
                list.add(singleLine);


                FCMapper.put(orderNum, list);

            }


        }catch (Exception e){
            System.out.println("=====================异常===============================" + count);
        }

        System.out.println("count = " + count);
        System.out.println(FCMapper.size());
        System.out.println(set.size());
        return FCMapper;
    }



    /**
     * 得到单元格的值
     * @param XSSFCell cell
     * @return cell单元格的值
     */
    private static String getValue(XSSFCell cell) {
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            // boolean类型
            return String.valueOf(cell.getBooleanCellValue()).trim();
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            // 数字类型  .数字单元格避免科学计数法
            DecimalFormat df = new DecimalFormat("0");
            return df.format(cell.getNumericCellValue()).trim();
        } else {
            // String类型
            return String.valueOf(cell.getStringCellValue()).trim();
        }
    }

}
